Advanced Search

SQL Server Backup and Restore Snippets

/ Published in: SQL
Save to your folder(s)
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 
  1. --Run on source server (Path is relative to server not client.)
  2.  
  3. 	USE master
  4. 	GO
  5. 	DECLARE @DestPath nvarchar(100) = '[MyPath]\myDB.bak'
  6. 	BACKUP DATABASE myDB
  7. 		TO DISK = @DestPath
  8. 		WITH copy_only;
  9. /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
  10. --Copy file to server Destination and Run:
  11. --C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data is default.
  12. 	USE master
  13. 	GO
  14. --gets data file names (Used to rename db.
  15. 	DECLARE @SourcePath nvarchar(100) = '[MyPath]\myDB.bak'
  16. 	DECLARE @DbStoragePath nvarchar(100) = '[MyPath]'
  17. 		RESTORE FILELISTONLY
  18. 		FROM DISK = @SourcePath;
  19. --Restore a the backed up db
  20. 	RESTORE DATABASE myNewDB
  21. 	FROM DISK = @SourcePath
  22. 	--optional:
  23. 	WITH MOVE 'myDB_Data' TO @DbStoragePath + '\myNewDB_Data.mdf',
  24. 	MOVE 'myDB_DataFile_Log' TO @DbStoragePath + '\myNewDB_Log.mdf';

URL: http://msdn.microsoft.com/en-us/library/ms186865.aspx

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.