Return to Snippet

Revision: 43452
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
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
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