Copy DB - different name - end of month

Question

Copy DB - different name - end of month

krypto69

SSChampion

Points: 13604
More actions
February 1, 2010 at 7:36 am

#220150

I have a DB that I need to make a copy of at the end of every month for archiving.
Both the source DB and the one I need to copy are on the same server.
Problem is I need to have the name of the DB be something that shows that it is the end of the month DB. ie (DB_NAME_01_31_10).
Whats the best way to automate this end of month process?

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Either you need to be using the Database Snapshots feature.. (There would be performance hit if your db is larger one, and you also need to have Enterprise Edition for this to work)

OR

Make a custom Job of taking a backup and restoring with an appended name schedule the job to execute monthly once.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

krypto69 SSChampion Points: 13604 More actions · Answer 2

Thanks for the reply.

I realize I'll need to make a custum job, but how do I have the job create a unique end of the month name for the DB every month?

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think the easiest is a full backup, and then a restore with a name that was dynamically created.

i don't think the backup command takes a variable for the database name, so i think you have to do it dynamically.

for sorting reasons, i used a different dateformat than you wanted, it produces results like this:

RESTORE DATABASE [SandBox_2010_02_01]

FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SandBox.bak'

WITH FILE = 1, NOUNLOAD, STATS = 10

here's the string builder:

BACKUP DATABASE [SandBox]

TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SandBox.bak'

WITH NOFORMAT, NOINIT, NAME = N'SandBox-Full Database Backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

declare @sql varchar(max)

SELECT @sql = 'RESTORE DATABASE [SandBox' + '_' + REPLACE(CONVERT(varchar,getdate(),111),'/','_')

+ ']

FROM DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SandBox.bak''

WITH FILE = 1, NOUNLOAD, STATS = 10'

print @sql

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Part of it depends on when (date and time) the end of month backup is taken and if that time is consistantly the same.

krypto69 SSChampion Points: 13604 More actions · Answer 5

Lowell,

I'm testing what you wrote..but have a quick question:

How do i tell the restore script to use different volumes to restore on to?

Looking at your script it doesn't tell the restore the (not usual) volumes to restore to.

Lowell SSC Guru Points: 323518 More actions · Answer 6

it's all part of the rESTORE command; i just fiddled with a backup command and scripted it;

i think you mean the MOVE command, right?:

RESTORE DATABASE [SandBox_2010]

FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\SandBox.bak'

WITH FILE = 1,

MOVE N'SandBox' TO N'c:\DATA\SandBox_2010.mdf',

MOVE N'SandBox_log' TO N'c:\SandBox_2010_1.LDF',

NOUNLOAD, STATS = 10

GO

so you'd add some scripting to use the MOVE command to place the new mdf/ldf somewhere else, that was wehat you were after, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 7

I like Lowell's idea. The problem with snapshots is they don't survive restores or issues with the system. They also would not survive a migration to another server. IMHO, archiving is a bad use of snapshots.

I'm not sure what you mean by different volume. Do you mean different disk drive? Or different path? You can calculate those values. Lowell's script builds a string to execute. Just break that and use a formula to change the information.

select @a = 'restore to mydb'

can easily become

select @a = restore to ' + Datename(mm, getdate()) + ' with move to disk = ' + @newdisk + ' \' + @newname

krypto69 SSChampion Points: 13604 More actions · Answer 8

I ran the script, with the move part added (thanks lowell,& steve)

But it didn't create my new end of month DB that I need.

Guessing there's a 'create DB' missing from the restore script?

I was hoping it would create a new DB named with the date. But it didn't create the new DB. I think it just overwrote the existing/original/source DB.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

If the database name already exists, the restore will not overwrite it unless you include the REPLACE clause in the RESTORE command. I didn't see that in the code provided by Lowell. Can you post the code (all of it) that you ran?

Lowell SSC Guru Points: 323518 More actions · Answer 10

my script just had print @sql; i did not include the final exec(@sql); sorry, i thought that was intuitive;oops!

just add that to the end of it;

I assumed that since we are building the db name dynamically, the db would not exist; Lynn has an excellent point, he said you should add that REPLACE command just in case.

note my script used getdate() for calculating the data; ifd you created a job like Bru suggested, that ran at the end of the month, the date would be the run date, you cna always change that to be a specific data;(end of month, last biz day of the end of month, etc)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

krypto69 SSChampion Points: 13604 More actions · Answer 11

Here's what I ran:

As an overview - I need to create a BAK file of the existing MSPHXX. Then, restore that onto my K and L voumes (DB & log) with a new DB name that includes the date as part of the new DB name:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

krypto69 (2/1/2010)
Here's what I ran:
As an overview - I need to create a BAK file of the existing MSPHXX. Then, restore that onto my K and L voumes (DB & log) with a new DB name that includes the date as part of the new DB name:

Okay, so what did you run?

krypto69 SSChampion Points: 13604 More actions · Answer 13

Having trouble posing the script....crazy...

I can post anything that isn't code?

I tried using the IFCode shortcuts...doesn't help.

krypto69 SSChampion Points: 13604 More actions · Answer 14

USE MASTER

BACKUP DATABASE [MSPHXX]

TO DISK = N'K:\MSPHXX_ARC.bak'

WITH NOFORMAT, NOINIT, NAME = N'MSPHXX_ARC',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

declare @ sql varchar(max)

SELECT @ sql = 'RESTORE DATABASE [MSPHXX_ARC' + '_' +

+ '] MOVE N'MSPHXX_ARC' TO N'K:\MSPHXX_ARC.MDF',

MOVE N'MSPHXX_ARC_LOG' TO N'L:\MSPHXX_ARC.LDF'

FROM DISK = N''K\MSPHXX_ARC.bak''

WITH FILE = 1, NOUNLOAD, STATS = 10'

EXEC @sql