Opens the permission to run xp_cmdshellĮXEC _configure 'show advanced options', 1ĮXEC _configure 'xp_cmdshell', 1 IF EXISTS (SELECT name FROM WHERE name = and is not null) IF Not EXISTS (SELECT name FROM WHERE name = Checks if the source database exists If Checks if the target database already exists If the source name is the same as the target name does not perform the operation Composes the names of the physical files for the new database Where d.name = and f.type_desc = 'ROWS' ) Set = (SELECT f.name LogicalName FROM sys.master_files f INNER JOIN sys.databases d ON d.database_id = f.database_id Clears the file name and leaves the directory name Set = (SELECT top(1) physical_name as dirįROM sys.master_files where DB_NAME(database_id) = ) Finds the physical location of the files on the disk SET = N'c:\temp' - The name of the temporary directory in which the temporary backup file will be saved SET = N'Northwind_copy' - The name of the target database SET = N'Northwind' - The name of the source database Copying the database is based on backing up the original database and restoring with a different name I relied on examples given on this site with some improvements. This program copies a database to the same server under a different name. 'CREATE DATABASE ' + + ' ON NVARCHAR(MAX) =
SQL COPY FILE AND RENAME IT OFFLINE
SELECT FROM ' + + '.sys.sysfiles')ĮXEC('ALTER DATABASE ' + + ' SET OFFLINE WITH ROLLBACK IMMEDIATE')ĮXEC('ALTER DATABASE ' + + ' SET SINGLE_USER')ĮXEC('EXEC sp_detach_db = ''' + + '''')ĭECLARE NVARCHAR(255), NVARCHAR(255), NVARCHAR(255), NVARCHAR(255), NVARCHAR(MAX) = NVARCHAR(MAX) =
Run script, but type your db names in and variables before.ĭECLARE NVARCHAR(255) = 'Products_branch'ĬREATE TABLE #DBFileNames( NVARCHAR(255)).Configure sql server for execute xp_cmdshelĮXEC sp_configure 'show advanced options', 1.It's not necessary, but maybe access denied error on executing. Run Managment Studio as Administrator account.
MOVE TO TO based on Joe answer ( detach, copy files, attach both). New DB name will have name = sourceDB_yyyy-mm-ddĭeclare nvarchar(50) = + '_' + (select convert(varchar(10),getdate(), 121))ĭeclare nvarchar(100) = + + '_log' + '.ldf'īACKUP DATABASE TO DISK = DATABASE FROM DISK = REPLACE, Just set settings: DB name, temp folder, db files folder.Īnd after run you will have the copy of DB with Name in "sourceDBName_yyyy-mm-dd" format.
0 kommentar(er)
