/ Published in: SQL
Backup by using Copy_Only. Use to make a copy of the db with or without a new name. Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. Copy_only option is 2005 on.
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
--Run on source server (Path is relative to server not client.) USE master GO DECLARE @DestPath nvarchar(100) = '[MyPath]\myDB.bak' BACKUP DATABASE myDB TO DISK = @DestPath WITH copy_only; /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ --Copy file to server Destination and Run: --C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data is default. USE master GO --gets data file names (Used to rename db. DECLARE @SourcePath nvarchar(100) = '[MyPath]\myDB.bak' DECLARE @DbStoragePath nvarchar(100) = '[MyPath]' RESTORE FILELISTONLY FROM DISK = @SourcePath; --Restore a the backed up db RESTORE DATABASE myNewDB FROM DISK = @SourcePath --optional: WITH MOVE 'myDB_Data' TO @DbStoragePath + '\myNewDB_Data.mdf', MOVE 'myDB_DataFile_Log' TO @DbStoragePath + '\myNewDB_Log.mdf';
URL: http://msdn.microsoft.com/en-us/library/ms186865.aspx