advanced code snippet search

Posted By

michanne on 03/24/11


Tagged

administration


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

plugables


SQL Server Backup and Restore Snippets

 / Published in: SQL
 

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

Backup by using CopyOnly. 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. Copyonly option is 2005 on.

Expand | Embed | Plain Text 
  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';

Report this snippet  

You need to login to post a comment.