asked on

Renaming Databases with Variable...

Hi,
I'm using Win2k Server w/SQLServer2K. I recieve a database dump every night and I use the restore function to put it in my SQL Server then I have to rename the Database to a date format ("03_05_03" for example). I want to know if there is anyway to automate this process. I would have to be able to rename a table with a variable (like the date).

In short
I need to do the following things automated...
1. Grab the Database dump like "MyData.bak"
2. Restore Database.
3. Rename that Database with the day's date.

I am a newbie to this and really can't find much on this.

Hope to hear from you soon...
Thank You for your time and patience,
nile88@comcast.net

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EugeneZ

try this:
--With this code (below) you can for example:create stored procedure then schedule job
-----------------------------------------------------

declare @dbName varchar(100)
declare @newdataPath varchar(100)
declare @newLogPath varchar(100)

set @dbName= 'TestDB_' + convert(varchar(10),getdate(),112)
set @newdataPath= 'c:\' + @dbName + '.mdf'
set @newLogPath= 'c:\' + @dbName + '.ldf'

BACKUP DATABASE Northwind

TO DISK = 'c:\Northwind.bak'

RESTORE DATABASE @dbName

FROM DISK = 'c:\Northwind.bak'

WITH MOVE 'Northwind' TO @newdataPath,

MOVE 'Northwind_log' TO @newLOgPath

GO
------------------------------------------------

CleanupPing

nile88:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.

monosodiumg

No comment has been added to this question in more than 245 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
Accept: angelIII http:#8087723

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer