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

Is there sql script or stored procedure or something simular I can use to create a copy and rename a sql server db?

A lot of my day is spent create new sql dbs via the UI

Is there a sql script or something simular I can use to speed this up for me, i.e. i'd pass in the name of an existing db, and a new name, and thescript would backup and create/restore a new db based on the new name?

THanks
Webbo
0
Webbo_1980
Asked:
Webbo_1980
1 Solution
 
devlab2012Commented:
use can use "create database", sp_renamedb stored procedure and "backup database" commands.
0
 
Webbo_1980Author Commented:
Thanks Devlab2012, but will that do the restore ?

Would it be possible to give me a quick examples if thats ok?
0
 
rmm2001Commented:
Try something like this. You'll have to set all of the variables to what you need though. You could put it in a stored proc and just pass in the variables too.
DECLARE @OrigDBName VARCHAR(100)
DECLARE @OrigDBDataName VARCHAR(100)
DECLARE @OrigDBLogName VARCHAR(100)
DECLARE @OrigDBNameBackup VARCHAR(100)

SET @OrigDBName = 'OrigDB'
SET @OrigDBDataName = 'OrigDB_Data'
SET @OrigDBLogName = 'OrigDB_Log'
SET @OrigDBNameBackup = 'OrigDBBackup'

DECLARE @RestoreDBName VARCHAR(100)
DECLARE @RestoreDBDataLocation VARCHAR(100)
DECLARE @RestoreDBLogLOcation VARCHAR(100)
DECLARE @RestoreDBNameBackup VARCHAR(100)

SET @RestoreDBName = 'NewDB'
SET @RestoreDBDataName = 'RestoreDB_Data'
SET @RestoreDBLogName = 'RestoreDB_Log'
SET @RestoreDBDataLocation = 'C:\Data\RestoreDB.mdf'
SET @RestoreDBNameLogLocation = 'C:\Data\RestoreDB.ldf'

DECLARE @SqlCmd VARCHAR(8000)

SET @SqlCmd = '
BACKUP DATABASE [' + @OrigDbName + ']
   TO ['+ @OrigDBNameBackup +']

RESTORE FILELISTONLY 
   FROM ['+ @OrigDBNameBackup +']

RESTORE DATABASE ['+ @RestoreDBName +'] 
   FROM ['+ @OrigDBNameBackup +'] 
   WITH MOVE ''' + @OrigDBDataName + ''' TO ''' + @RestoreDBDataLocation + ''',
   MOVE ''' + @OrigDBLogName + ''' TO ''' + @RestoreDBNameLogLocation'''
GO'

EXEC (@SqlCmd)

Open in new window

0

Featured Post

Industry Leaders: 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