Revision: 43452
Updated Code
at March 24, 2011 04:02 by michanne
Updated Code
--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';
Revision: 43451
Updated Code
at March 24, 2011 03:50 by michanne
Updated Code
--Run on source server (Path is relative to server not client.) use master go Declare @DestPath = '[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 = '[MyPath]\myDB.bak' Declare @DbStoragePath = '[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';
Revision: 43450
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at March 24, 2011 03:38 by michanne
Initial Code
--Run on source server use master go Declare @DestPath = '[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 = '[MyPath]\myDB.bak' Declare @DbStoragePath = '[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';
Initial URL
http://msdn.microsoft.com/en-us/library/ms186865.aspx
Initial Description
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.
Initial Title
SQL Server Backup and Restore Snippets
Initial Tags
Initial Language
SQL