• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

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
0
nile88
Asked:
nile88
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1) I think you know that already
2) that also, although you might want to restore the database directly to the final name instead of restoring+renaming it. Note that the RESTORE command doesn't expect the "old" name of the database that was backed up, but the new name.
3) the stored procedure sp_renamedb should help you to rename a database.

To automate this, you will probably need a scheduler, where you can take either the Windows Scheduler (scheduled tasks), or use the SQL Server Agent to schedule a job.

CHeers
0
 
Eugene ZCommented:
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
------------------------------------------------
0
 
CleanupPingCommented:
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.
0
 
monosodiumgCommented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now