Copy DB - different name - end of month

Question

Copy DB - different name - end of month

Viewing 11 posts - 16 through 25 (of 25 total)

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

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

krypto69 (2/1/2010)
Having trouble posing the script....crazy...
I can post anything that isn't code?
I tried using the IFCode shortcuts...doesn't help.

Have a declare statement in your code? Try putting a space between the @ sign and the variable name. That is what I have found I need to do here at work.

krypto69 SSChampion Points: 13604 More actions · Answer 2

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

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think this is pretty close; renaming the new db and it's files:

it gives me this for the print statement:

RESTORE DATABASE [MSPHXX_ARC_2010_02_01] MOVE N'MSPHXX_ARC' TO N'K:\MSPHXX_ARC_2010_02_01.MDF',

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

FROM DISK = N'K\MSPHXX_ARC.bak'

WITH FILE = 1,

NOUNLOAD,

REPLACE,

STATS = 10

stringbuilder:

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' + '_' + REPLACE(CONVERT(varchar,getdate(),111),'/','_')

+ '] MOVE N''MSPHXX_ARC'' TO N''K:\MSPHXX_ARC' + '_' + REPLACE(CONVERT(varchar,getdate(),111),'/','_') + '.MDF'',

MOVE N''MSPHXX_ARC_LOG'' TO N''L:\MSPHXX_ARC' + '_' + REPLACE(CONVERT(varchar,getdate(),111),'/','_') + '.LDF''

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

WITH FILE = 1,

NOUNLOAD,

REPLACE,

STATS = 10'

print @sql

exec(@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!

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

I've edited your code. You highlight it and then click the " on the left to format it.

What you should do is not "execute" the code, but do the print as Lowell had listed. Look at the code that prints and tweak it. In the code you need to use a forumula to calculate the name (with date) for both the db and the file name. It's not hard, but you need to play with it and make sure it's what you want. The code for the restore is simple, and there are examples in BOL.

You don't want to run code like this without PRINTing it first and making sure it's what you expect to execute. You can manually execute the printed code

krypto69 SSChampion Points: 13604 More actions · Answer 5

Thanks guys..

I'm working on everything you wrote...

but

quick question, using the script you guys have written, I keep adding on to the existing bak file (on K:\)...which I don't want.

how can I delete the existing bak file before I run the rest of the script?

Lowell SSC Guru Points: 323518 More actions · Answer 6

change the backup command so instead of NOINIT it says INIT:

NOINIT=append to existing

INIT=replace if exists:

USE MASTER

BACKUP DATABASE [MSPHXX]

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

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

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

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 7

When I run the restore part -

I get an error

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'MSPHXX_ARC'.

Msg 319, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

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

krypto69 (2/10/2010)
When I run the restore part -
I get an error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MSPHXX_ARC'.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Show us your code. Can't tell you what's wrong if we can't see it.

krypto69 SSChampion Points: 13604 More actions · Answer 9

Sorry Lynn but I can't post code...something wierd @ work stops me from posting code...tried everything

but if you look in this post..it's the restore part of the statement that Lowell posted

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

Have similar issues here at times. Cut and paste to a text editor, save as a .txt file and then upload the file to SSC.

krypto69 SSChampion Points: 13604 More actions · Answer 11

damn ....can't upload a txt file either....

im using the part of Lowells scriipt in this post starts with declare @sql varchar(max)