In your current job, you need to address the issue of replicating the entire SQL Server database, which includes the database outline, stored procedures in the database, functions, table structure, primary foreign key relationships, and all the data in the table, which means the copy version is identical to the original database. After a period of exploration, found a relatively simple solution is:
(1) Back up the database to the file before copying the database.
(2) Create a new database based on the backup file and restore it.
The backup database can be SQL statements as follows:
String. Format ("Backup database {0} to disk = ' {1} ';", dbname, Bakfilepath) Create and restore a new database based on backup files can be implemented using the following stored procedures:
Copy Code code as follows:
CREATE PROCEDURE Copydb
(
@newDbName varchar (50),--New database name
@dbDataDirPath varchar (100),--Data folder directory path for database installation
@soureDbName varchar (100),--source database name
@soureBackupFilePATH varchar (100)--the path of the source database backup file
)
As
DECLARE @sql varchar (3000)
Set @sql = '
Create DATABASE ' + @newDbName + '
On
(
Name= ' + @soureDbName + ' _data,
Filename= ' + @dbDataDirPath + @newDbName + ' _data.mdf ',
SIZE = 10,
FileGrowth = 15%
)
LOG on
(
Name= ' + @soureDbName + ' _log ',
Filename= ' + @dbDataDirPath + @newDbName + ' _log.ldf ',
SIZE = 5MB,
MAXSIZE = 25MB,
FileGrowth = 5MB
)
--Start the Restore
RESTORE DATABASE ' + @newDbName + ' from disk= ' + @soureBackupFilePATH + ' "' with REPLACE
'
EXEC (@sql)
Go
