restore databases in T-SQL.

--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('d')
GO
Am I correct in creating the database and the way of backup?
Would you teach me the senarios that restore the database to 'a','b','c','d'
states?
Thanks in advance.You'll need to add another backup to backup 'd', but something like this
should do:
--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('d')
GO
--whole backup 3
BACKUP DATABASE Test TO mydiskdump
go
restore headeronly from mydiskdump
go
restore database Test from mydiskdump with file = 1, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 1, norecovery
restore database Test from mydiskdump with file = 2, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 3, norecovery
restore database Test from mydiskdump with file = 4, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 5, recovery
select * from Test.dbo.tblTest
go
Note that dump devices can store multiple backup sets. You can get the
backup sets that a dump device contains using the restore headeronly command
which lets you know which backup sets to restore. I'd suggest you consider
naming your backup sets though as this can help work out which backups to
restore as well..
HTH
Regards,
Greg Linwood
SQL Server MVP
"haode" <haode@.hao.com> wrote in message
news:%23eNTtec9DHA.1504@.TK2MSFTNGP12.phx.gbl...
> --Drop database
> if exists (select * from master.dbo.sysdatabases where name='Test')
> drop database Test
> GO
> --Drop device
> if exists (select * from master.dbo.sysdevices where name='mydiskdump')
> exec master.dbo.sp_dropdevice mydiskdump
> GO
> --Create Test database
> CREATE DATABASE Test
> ON
> ( NAME = Test_dat,
> FILENAME = 'c:\Test.mdf',
> SIZE = 5,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
> LOG ON
> ( NAME = 'Test_log',
> FILENAME = 'c:\Test.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
> GO
> --add device
> EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
> GO
> CREATE TABLE Test.dbo.tblTest (
> col1 char(1)
> )
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values ('a')
> GO
> --whole backup 1
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('b')
> GO
> --differential backup 1
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> --whole backup 2
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('c')
> GO
> --differential backup 2
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('d')
> GO
> Am I correct in creating the database and the way of backup?
> Would you teach me the senarios that restore the database to
'a','b','c','d'
> states?
> Thanks in advance.
>