Backing Up, Archiving, Creating, Copying/Restoring

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Backing Up, Archiving, Creating, Copying/Restoring

Author

Topic

meemo
Starting Member

9 Posts

Posted - 2011-07-26 : 16:52:18

Hey,

I'm trying to do the following tasks in the order that it is mentioned, but I'm having trouble on step 2. Please note, some of the syntax comes from using SQL Studio which generates the script after clicking on some options.

I have 2 DBs on the same server: DBX and DBY.

Both are exactly the same.

Once a year, I need to take a copy of DBX and put it to DBY, overwriting it. However, just before I do that I need to archive what is in DBY, and save it to a new DB, called DBZ. Then I need to re-create DBY and copy over the data from DBX over to it.

Therefore, my steps would be:

1. DBX -> back it up
2. DBY -> rename to DBZ
3. DBY gets created again
4. DBY -> data is restored to it from the back up in step 1

The issue lies where the data/log files are not physically copied. Is there a way in sql to physically copy the log files? Or can you suggest another way to do something like this? Thanks.

The actual code...

--------------------------------------------------------
1. DBX -> back it up
--------------------------------------------------------
USE master;
GO
BACKUP DATABASE [DBX]
TO DISK = N'D:\MSSQL\Backup\DBX_TEST.bak'
WITH COPY_ONLY, FORMAT, INIT, MEDIANAME = N'DBX',
NAME = N'DBX Backup', SKIP,
NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DBX' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBX' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBX'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\MSSQL\Backup\DBX_TEST.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

--------------------------------------------------------
2. DBY -> rename to DBZ
--------------------------------------------------------
-- change the db name
ALTER DATABASE [DBY]
MODIFY NAME = [DBZ]

-- put it offline
ALTER DATABASE [DBZ]
SET offline
GO

-- move data file
ALTER DATABASE [DBZ]
MODIFY FILE (NAME = 'PER_Data', FILENAME = 'D:\MSSQL\Data\DBZ.mdf');

-- move log file
ALTER DATABASE [DBZ]
MODIFY FILE (NAME = 'PER_Log', FILENAME = 'D:\MSSQL\Data\DBZ.ldf');

-- put it online
ALTER DATABASE [DBZ]
SET online
GO

--------------------------------------------------------
3. DBY gets created again
--------------------------------------------------------
CREATE DATABASE [DBY]

--------------------------------------------------------
4. DBY -> data is restored to it from the back up in step 1
--------------------------------------------------------
USE master;
GO
RESTORE DATABASE [DBY]
FROM DISK = N'D:\MSSQL\Backup\DBX_TEST.bak'
WITH FILE = 1,
MOVE N'PER_Data' TO N'D:\MSSQL\Data\DBY.mdf',
MOVE N'PER_Log' TO N'D:\MSSQL\Data\DBY.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-07-27 : 15:52:33

How r u running these steps? through job or individually in query analyzer?
If running individually then use os command to copy data/log files to new location after putting db off line. Once files copied to new location then use alter db modify command.
If running through job just add one step after putting databse offline that will use os command to copy files to new location.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources