CODEWITHSUNDEEP
sqlsql-serversql-server-2005backup
cdub
cdub

Reputation: 25711

Copying a SQL Server database to another database

I have a database in SQL server 2005 that is called MyStaging and I made a blank one called MyStaging_bk. I went to Backup MyStaging and then tried to restore in to MyStaging_bk. But it says I cannot restore since the backup sets are different.

How do I copy db's and they are on the same database server?

Upvotes: 1

Views: 165

Answers (1)

lolol
lolol

Reputation: 4390

I'm considering you can log into the sql server with a high level user.

-- go for master
use master

-- backup the old database
backup database olddatabasename
to disk = 'c:\backup.bak'

-- create a new database
create database newdatabasename
on
(
    name = newdatabasenamedata,
    filename = 'c:\newdatabasenamedata.mdf',
    size = 500mb,
    maxsize = unlimited,
    filegrowth = 25mb
)
log on
(
    name = newdatabasenamelog,
    filename = 'c:\newdatabasenamelog.ldf',
    size = 5mb,
    filegrowth = 5mb
)

-- put it in single user mode
alter database newdatabasename
set single_user
with rollback immediate

-- get the backup logical names and write it down
restore filelistonly
from disk = 'c:\backup.bak'

-- restore the database from the backup using the logical names
restore database newdatabasename
from disk = 'c:\backup.bak'
with
    move 'olddatabasenamelogicalnamedata' to 'c:\newdatabasenamedata.mdf',
    move 'olddatabasenamelogicalnamelog' to 'c:\newdatabasenamelog.ldf',
    replace

-- rename the logical names if you want (do it =p)
alter database newdatabasename
modify file
(
    name = 'olddatabasenamelogicalnamedata',
    newname = 'newdatabasenamedata'
)

alter database newdatabasename
modify file
(
    name = 'olddatabasenamelogicalnamelog',
    newname = 'newdatabasenamelog'
)

I'm pretty sure that restoring a database from backup automatically puts it in multi user, but:

alter database newdatabasename
set multi_user

The newdatabase don't have any user added to it. So:

use newdatabasename    

create user someuser from login anexistentloginname

sp_addrolemember 'db_owner', 'someuser'
sp_addrolemember 'db_datareader', 'someuser' 
sp_addrolemember 'db_datawriter', 'someuser'

UPDATE1:

You have to run each block separately or add the go keyword in each block.

UPDATE2:

My bad here, the newdatabase will get all users from the backup database.

Upvotes: 3

Related Questions