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.

  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.