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.


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.