Hi,
Our current backup process writes backup to a network drive and that is
transferred to a DVD on a daily basis. How can we prevent people from
installing this backup on the DVD if the DVD is ever stolen? Can the
backups be "password protected" or restricted to prevent un-authorized
restore?
Thanks.From BooksOnLine under BACKUP:
PASSWORD = { password | @.password_variable }
Sets the password for the backup set. PASSWORD is a character string. If a
password is defined for the backup set, the password must be supplied to
perform any restore operation from the backup set.
This may prevent most people from restoring the backup but does nothing to
prevent anyone from reading the file with a simple hex editor. You should
look at one of the 3rd party backup tools that do encryption among other
things.
www.red-gate.com
www.quest.com
Andrew J. Kelly SQL MVP
<RK> wrote in message news:%23Evnr5uIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Our current backup process writes backup to a network drive and that is
> transferred to a DVD on a daily basis. How can we prevent people from
> installing this backup on the DVD if the DVD is ever stolen? Can the
> backups be "password protected" or restricted to prevent un-authorized
> restore?
> Thanks.
>|||Windows has extensive cryptography capabilities that allow creation of
encryption and decryption software relatively easily. If relevant to your
requirements, there are many programs available at little or no cost that
can encrypt and decrypt the backup file(s) separately.
<RK> wrote in message news:%23Evnr5uIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Our current backup process writes backup to a network drive and that is
> transferred to a DVD on a daily basis. How can we prevent people from
> installing this backup on the DVD if the DVD is ever stolen? Can the
> backups be "password protected" or restricted to prevent un-authorized
> restore?
> Thanks.
>
Showing posts with label basis. Show all posts
Showing posts with label basis. Show all posts
Thursday, March 22, 2012
Backup Schedule
Hi
I want to take a backup of SQL Server database on weekly
basis. How can I set up things on my server ? any script
will have to be written or any tool to be used ? pl.
help..
JayHi,
You can use database maintenance plans (Enterprise Manager) to create a
backup job which can be scheduled to execute every week once.
Thanks
Hari
MCDBA
"Jay" <neessan_gen@.rediffmail.com> wrote in message
news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||In addition to Hari's post:
Weekly? That would never go in my installation. You are essentially saying that it is OK to lose up
to one week worth of data. My baseline is database backup every day and log backup every hour.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jay" <neessan_gen@.rediffmail.com> wrote in message news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||Create a Database Manitance Plan or create job which should start on weekly.
--
Shaju Thomas
"Jay" <neessan_gen@.rediffmail.com> wrote in message
news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||THANKS HARI and SHAJU, appreciate your help.
Jay
>--Original Message--
>Hi,
>You can use database maintenance plans (Enterprise
Manager) to create a
>backup job which can be scheduled to execute every week
once.
>Thanks
>Hari
>MCDBA
>
>"Jay" <neessan_gen@.rediffmail.com> wrote in message
>news:048201c39daf$d6959230$a501280a@.phx.gbl...
>> Hi
>> I want to take a backup of SQL Server database on weekly
>> basis. How can I set up things on my server ? any script
>> will have to be written or any tool to be used ? pl.
>> help..
>> Jay
>
>.
>|||HI
We hawe 30 database on 3 server with multiple backup strategy. (1-30GB)
Main backup procedure: Backup database to device on network share. Backup the devices to tape.
Customized backup:
Purpose: Reduce nigtly backup time. One backup script for Multi server environment (MSX-TSX).
Easy configuration for new or migrated databases.
Reduce disk usage.
Easy restore. (We can futurely write one script with parameters for restore)
Monday - Saturday
1. - 4 logbackup / day and differential backup 22:00
2. - 4 logbackup / day and differential backup 20:00
3. - 3 logbackup / day and differential backup 20:00
4. - 1 logbackup / day and differential backup 20:00
5. - differential backup 20:00
6. - differential backup 22:00
7. - full backup 20:00 (include master and msdb)
Sunday full backup instead of differential backup
Implementation:
Create registry entry for Bakup folder (eg: HKEY_LOCAL_MACHINE\SOFTWARE\Vodafone\MSSQLbackup)
Create table in msdb:
/********************************************************/
CREATE TABLE Backups (
dbName varchar (255) COLLATE Hungarian_CI_AS NOT NULL , --Database names
MainBKP int NULL --Backup configuration identifier
) ON PRIMARY
GO
/********************************************************/
Populate this table with database name and backup id
Create the following sp in master database:
The Backup script:
/********************************************************/
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS ON GO
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
-- AdmSpBackup: r=F6vid le=EDr=E1s
----
-- Param=E9terek:
--
----
-- T=E1bl=E1k:
-- Haszn=E1lt t=E1bl=E1k neve, hozz=E1f=E9r=E9s m=F3dja
-- ----
-- Hivatkoz=F3 elj=E1r=E1sok:
-- Mely elj=E1r=E1sb=F3l ker=FCl megh=EDv=E1sra ----
-- H=EDvott elj=E1r=E1sok:
-- Mely elj=E1r=E1sokat h=EDv meg
--
----
-- Visszat=E9r=E9si =E9rt=E9kek:
-- =C9rt=E9k: 0, ha sikeres, -101 ha hib=E1s a fut=E1s
-- Eredm=E9nyhalmaz:
-- ----
-- Tranzakci=F3k:
-- Nem haszn=E1l tranzakci=F3t
----
-- R=E9szletes le=EDr=E1s:
-- ----
-- Jegyzet: ----
-- K=E9sz=EDtette: Jakus Andr=E1s, 2003-10-20
----
-- T=F6rt=E9net:
-- -- --=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
ALTER PROCEDURE AdmSpBackup
AS
SET NOCOUNT ON
SET DATEFIRST 1
DECLARE @.set INT
DECLARE @.sql NVARCHAR(4000)
DECLARE @.name VARCHAR(100)
DECLARE @.main INT
DECLARE @.device VARCHAR(255)
DECLARE @.defPath VARCHAR(255)
DECLARE @.dbPath VARCHAR(255)
DECLARE @.tmpStr VARCHAR(200)
DECLARE @.message VARCHAR(8000)
DECLARE @.Err int
DECLARE @.SUBJECT VARCHAR(100)
---
---
SET @.set =3D DATEPART(HH, GETDATE())
SET @.message =3D 'Starting No: ' + CAST(@.set AS VARCHAR(2)) + ' backup on: ' + @.@.SERVERNAME + ' ' + CONVERT(VARCHAR
(100), GETDATE(), 20) + Char(13) + Char(13)
SELECT @.sql =3D CASE WHEN @.set =3D 7 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2)'
WHEN @.set =3D 12 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2, 3)'
WHEN @.set =3D 16 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2, 3)'
WHEN @.set =3D 20 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (2, 3, 4, 5, 7)'
WHEN @.set =3D 22 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 6)'
ELSE 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP =3D -1'
END
EXEC sp_executesql @.sql
---
----
SET @.tmpStr =3D REPLACE(@.@.SERVERNAME, '\', '-')
-- A registry-ben be=E1ll=EDtott ment=E9si k=F6nyvt=E1r
EXEC master..xp_regread @.rootkey=3D'HKEY_LOCAL_MACHINE',
@.key=3D'SOFTWARE\Vodafone\MSSQLbackup',
@.value_name =3D @.tmpStr,
@.value=3D@.defPath OUTPUT
-- Van-=E9 k=F6nyvt=E1ra az adatb=E1zisnak
SET @.tmpStr =3D 'master..xp_subdirs ''' + @.defPath + ''''
CREATE TABLE #Dirs (a VARCHAR(100))
insert INTO #Dirs (a) EXEC(@.tmpStr)
---
----
DECLARE bcks CURSOR FOR SELECT * FROM ##dbs
OPEN bcks
FETCH NEXT FROM bcks
INTO @.name, @.main
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
/**---
---**/
/** Verify, that have subfolder for database **/
/**---
---**/
If @.name not in (select a from #Dirs)
BEGIN
=09
SET @.tmpStr =3D 'Mkdir "' + @.defPath + @.name + '"' --
Create subfolder for database
=09
EXECUTE xp_cmdshell @.tmpStr
=09
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: ' + @.tmpStr + Char(13) + Char
(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup folder: ' + @.defPath + @.name + Char(13) + Char
(13)
END
=09
END
/**---
---**/
/** Verify, that have Backup media for full database backup **/
/**---
---**/
If @.Name + '-Full' not in (SELECT logical_device_name from msdb.dbo.backupmediafamily)
BEGIN
SET @.device =3D @.Name + '-Full'
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
=09
=09
=09
BEGIN
=09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(13) + Char(13)
BACKUP DATABASE @.Name
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
END
ELSE
BEGIN
/**---
---
If @.main < 5 then backup log for @.Name
---
---**/
If @.Main < 5
BEGIN
SET @.device =3D @.Name + '-Log' + CAST(@.set AS VARCHAR(2))
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.trn'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
END
=09
=09
END
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Log ' + @.Name + ' started' + Char(13) + Char(13)
BACKUP LOG @.Name=09
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP LOG ' + @.Name + Char
(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' log successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
END
/**---
---
If @.set > 19 then backup database @.Name
If DATEPART(dw, GETDATE()) =3D 7 Then Full backup
---
---**/
If DATEPART(dw, GETDATE()) =3D 7 AND @.set > 19 --@.Name NOT IN ('master', 'msdb')
BEGIN =09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(13) + Char(13)
SET @.device =3D @.Name + '-Full'
=09
BACKUP DATABASE @.Name
TO @.device
WITH
RETAINDAYS =3D 6,
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
ELSE
/**---
---
If DATEPART(dw, GETDATE()) < 7 Then Differential backup, @.main < 7 ---
---**/
BEGIN
If @.main < 7 AND @.set > 19
BEGIN
SET @.device =3D @.Name + '-Diff'
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
=09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Differential Backup)' + Char(13) + Char(13)
BACKUP DATABASE @.Name TO @.device
WITH DIFFERENTIAL,
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS varchar(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
ELSE
BEGIN
SET @.device =3D @.Name + 'Full'
If @.set > 19 BEGIN
If @.device not in (select LTRIM
(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS varchar(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
=09
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(10) + Char(13)
BACKUP DATABASE @.Name
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST
(@.Err AS varchar(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
END
END
/**-- next database --
---**/
END
FETCH NEXT FROM bcks
INTO @.name, @.main
END
CLOSE bcks
DEALLOCATE bcks
/**---
---**/
/** Send message **/
/**---
---**/
SET @.message =3D @.message + 'Completed No: ' + CAST(@.set AS VARCHAR(2)) + ' backup on: ' + @.@.SERVERNAME + ' ' + CONVERT(VARCHAR(100), GETDATE(), 20)
SET @.SUBJECT =3D 'Backup report from ' + @.@.SERVERNAME
EXECUTE xp_sendmail @.Recipients =3D 'andras.jakus@.vodafone.com', --Your address here
@.Message =3D @.message,
@.Subject =3D @.SUBJECT
/**---
---**/
/** Drop alltemporary tables **/
/**---
---**/
DROP TABLE ##dbs
DROP TABLE #Dirs
/*******************************************************/
Change @.Recipients to your address
Create (Multi Server)job with one step: EXECUTE master.dbo.AdmSpBackup
Schedule the job: 07:00; 12:00; 16:00; 20:00; 22:00
>--Original Message--
>In addition to Hari's post:
>Weekly? That would never go in my installation. You are essentially saying that it is OK to lose up
>to one week worth of data. My baseline is database backup every day and log backup every hour.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sqlserver
>
>"Jay" <neessan_gen@.rediffmail.com> wrote in message news:048201c39daf$d6959230$a501280a@.phx.gbl...
>> Hi
>> I want to take a backup of SQL Server database on weekly
>> basis. How can I set up things on my server ? any script
>> will have to be written or any tool to be used ? pl.
>> help..
>> Jay
>
>.
>sql
I want to take a backup of SQL Server database on weekly
basis. How can I set up things on my server ? any script
will have to be written or any tool to be used ? pl.
help..
JayHi,
You can use database maintenance plans (Enterprise Manager) to create a
backup job which can be scheduled to execute every week once.
Thanks
Hari
MCDBA
"Jay" <neessan_gen@.rediffmail.com> wrote in message
news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||In addition to Hari's post:
Weekly? That would never go in my installation. You are essentially saying that it is OK to lose up
to one week worth of data. My baseline is database backup every day and log backup every hour.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jay" <neessan_gen@.rediffmail.com> wrote in message news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||Create a Database Manitance Plan or create job which should start on weekly.
--
Shaju Thomas
"Jay" <neessan_gen@.rediffmail.com> wrote in message
news:048201c39daf$d6959230$a501280a@.phx.gbl...
> Hi
> I want to take a backup of SQL Server database on weekly
> basis. How can I set up things on my server ? any script
> will have to be written or any tool to be used ? pl.
> help..
> Jay|||THANKS HARI and SHAJU, appreciate your help.
Jay
>--Original Message--
>Hi,
>You can use database maintenance plans (Enterprise
Manager) to create a
>backup job which can be scheduled to execute every week
once.
>Thanks
>Hari
>MCDBA
>
>"Jay" <neessan_gen@.rediffmail.com> wrote in message
>news:048201c39daf$d6959230$a501280a@.phx.gbl...
>> Hi
>> I want to take a backup of SQL Server database on weekly
>> basis. How can I set up things on my server ? any script
>> will have to be written or any tool to be used ? pl.
>> help..
>> Jay
>
>.
>|||HI
We hawe 30 database on 3 server with multiple backup strategy. (1-30GB)
Main backup procedure: Backup database to device on network share. Backup the devices to tape.
Customized backup:
Purpose: Reduce nigtly backup time. One backup script for Multi server environment (MSX-TSX).
Easy configuration for new or migrated databases.
Reduce disk usage.
Easy restore. (We can futurely write one script with parameters for restore)
Monday - Saturday
1. - 4 logbackup / day and differential backup 22:00
2. - 4 logbackup / day and differential backup 20:00
3. - 3 logbackup / day and differential backup 20:00
4. - 1 logbackup / day and differential backup 20:00
5. - differential backup 20:00
6. - differential backup 22:00
7. - full backup 20:00 (include master and msdb)
Sunday full backup instead of differential backup
Implementation:
Create registry entry for Bakup folder (eg: HKEY_LOCAL_MACHINE\SOFTWARE\Vodafone\MSSQLbackup)
Create table in msdb:
/********************************************************/
CREATE TABLE Backups (
dbName varchar (255) COLLATE Hungarian_CI_AS NOT NULL , --Database names
MainBKP int NULL --Backup configuration identifier
) ON PRIMARY
GO
/********************************************************/
Populate this table with database name and backup id
Create the following sp in master database:
The Backup script:
/********************************************************/
SET QUOTED_IDENTIFIER OFF GO
SET ANSI_NULLS ON GO
--=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
-- AdmSpBackup: r=F6vid le=EDr=E1s
----
-- Param=E9terek:
--
----
-- T=E1bl=E1k:
-- Haszn=E1lt t=E1bl=E1k neve, hozz=E1f=E9r=E9s m=F3dja
-- ----
-- Hivatkoz=F3 elj=E1r=E1sok:
-- Mely elj=E1r=E1sb=F3l ker=FCl megh=EDv=E1sra ----
-- H=EDvott elj=E1r=E1sok:
-- Mely elj=E1r=E1sokat h=EDv meg
--
----
-- Visszat=E9r=E9si =E9rt=E9kek:
-- =C9rt=E9k: 0, ha sikeres, -101 ha hib=E1s a fut=E1s
-- Eredm=E9nyhalmaz:
-- ----
-- Tranzakci=F3k:
-- Nem haszn=E1l tranzakci=F3t
----
-- R=E9szletes le=EDr=E1s:
-- ----
-- Jegyzet: ----
-- K=E9sz=EDtette: Jakus Andr=E1s, 2003-10-20
----
-- T=F6rt=E9net:
-- -- --=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
ALTER PROCEDURE AdmSpBackup
AS
SET NOCOUNT ON
SET DATEFIRST 1
DECLARE @.set INT
DECLARE @.sql NVARCHAR(4000)
DECLARE @.name VARCHAR(100)
DECLARE @.main INT
DECLARE @.device VARCHAR(255)
DECLARE @.defPath VARCHAR(255)
DECLARE @.dbPath VARCHAR(255)
DECLARE @.tmpStr VARCHAR(200)
DECLARE @.message VARCHAR(8000)
DECLARE @.Err int
DECLARE @.SUBJECT VARCHAR(100)
---
---
SET @.set =3D DATEPART(HH, GETDATE())
SET @.message =3D 'Starting No: ' + CAST(@.set AS VARCHAR(2)) + ' backup on: ' + @.@.SERVERNAME + ' ' + CONVERT(VARCHAR
(100), GETDATE(), 20) + Char(13) + Char(13)
SELECT @.sql =3D CASE WHEN @.set =3D 7 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2)'
WHEN @.set =3D 12 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2, 3)'
WHEN @.set =3D 16 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 2, 3)'
WHEN @.set =3D 20 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (2, 3, 4, 5, 7)'
WHEN @.set =3D 22 THEN 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP IN (1, 6)'
ELSE 'SELECT dbName, MainBKP INTO ##dbs FROM msdb.dbo.Backups WHERE MainBKP =3D -1'
END
EXEC sp_executesql @.sql
---
----
SET @.tmpStr =3D REPLACE(@.@.SERVERNAME, '\', '-')
-- A registry-ben be=E1ll=EDtott ment=E9si k=F6nyvt=E1r
EXEC master..xp_regread @.rootkey=3D'HKEY_LOCAL_MACHINE',
@.key=3D'SOFTWARE\Vodafone\MSSQLbackup',
@.value_name =3D @.tmpStr,
@.value=3D@.defPath OUTPUT
-- Van-=E9 k=F6nyvt=E1ra az adatb=E1zisnak
SET @.tmpStr =3D 'master..xp_subdirs ''' + @.defPath + ''''
CREATE TABLE #Dirs (a VARCHAR(100))
insert INTO #Dirs (a) EXEC(@.tmpStr)
---
----
DECLARE bcks CURSOR FOR SELECT * FROM ##dbs
OPEN bcks
FETCH NEXT FROM bcks
INTO @.name, @.main
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
/**---
---**/
/** Verify, that have subfolder for database **/
/**---
---**/
If @.name not in (select a from #Dirs)
BEGIN
=09
SET @.tmpStr =3D 'Mkdir "' + @.defPath + @.name + '"' --
Create subfolder for database
=09
EXECUTE xp_cmdshell @.tmpStr
=09
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: ' + @.tmpStr + Char(13) + Char
(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup folder: ' + @.defPath + @.name + Char(13) + Char
(13)
END
=09
END
/**---
---**/
/** Verify, that have Backup media for full database backup **/
/**---
---**/
If @.Name + '-Full' not in (SELECT logical_device_name from msdb.dbo.backupmediafamily)
BEGIN
SET @.device =3D @.Name + '-Full'
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
=09
=09
=09
BEGIN
=09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(13) + Char(13)
BACKUP DATABASE @.Name
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
END
ELSE
BEGIN
/**---
---
If @.main < 5 then backup log for @.Name
---
---**/
If @.Main < 5
BEGIN
SET @.device =3D @.Name + '-Log' + CAST(@.set AS VARCHAR(2))
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.trn'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
END
=09
=09
END
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Log ' + @.Name + ' started' + Char(13) + Char(13)
BACKUP LOG @.Name=09
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP LOG ' + @.Name + Char
(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' log successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
END
/**---
---
If @.set > 19 then backup database @.Name
If DATEPART(dw, GETDATE()) =3D 7 Then Full backup
---
---**/
If DATEPART(dw, GETDATE()) =3D 7 AND @.set > 19 --@.Name NOT IN ('master', 'msdb')
BEGIN =09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(13) + Char(13)
SET @.device =3D @.Name + '-Full'
=09
BACKUP DATABASE @.Name
TO @.device
WITH
RETAINDAYS =3D 6,
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
ELSE
/**---
---
If DATEPART(dw, GETDATE()) < 7 Then Differential backup, @.main < 7 ---
---**/
BEGIN
If @.main < 7 AND @.set > 19
BEGIN
SET @.device =3D @.Name + '-Diff'
If @.device not in (select LTRIM(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS VARCHAR(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
=09
SET @.message =3D @.message + CONVERT(VARCHAR
(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Differential Backup)' + Char(13) + Char(13)
BACKUP DATABASE @.Name TO @.device
WITH DIFFERENTIAL,
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS varchar(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char
(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
ELSE
BEGIN
SET @.device =3D @.Name + 'Full'
If @.set > 19 BEGIN
If @.device not in (select LTRIM
(RTRIM(name)) from sysdevices where cntrltype =3D 2)
BEGIN
SET @.dbPath =3D @.defPath + @.Name + '\' + @.device + '.bak'
SELECT @.dbPath
=09
EXECUTE sp_addumpdevice @.devtype =3D 'Disk'
, @.logicalname =3D @.device
, @.physicalname =3D @.dbPath
, @.cntrltype =3D 2
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST(@.Err AS varchar(30)) + 'Command: sp_addumpdevice ' + @.device + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Successfully created a new backup device: ' + @.device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
=09
END
=09
SET @.message =3D @.message + CONVERT
(VARCHAR(100), GETDATE(), 20) + ': Backup Database ' + @.Name + ' started (Full Backup)' + Char(10) + Char(13)
BACKUP DATABASE @.Name
TO @.device
WITH
INIT
SET @.Err =3D @.@.ERROR
If @.Err !=3D 0
BEGIN SET @.message =3D @.message + CONVERT(VARCHAR(100), GETDATE(), 20) + ': Error: ' + CAST
(@.Err AS varchar(30)) + 'Command: BACKUP DATABASE ' + @.Name + Char(13) + Char(13)
END
ELSE
BEGIN
SET @.message =3D @.message + CONVERT(VARCHAR(100), GETDATE(), 20) + ': '+ @.Name + ' database successfully backed up to: ' + @.Device + Char(13) + Char(13)
SET @.message =3D @.message + 'Located: ' + @.defPath + @.name + Char(13) + Char(13)
END
=09
END
END
END
/**-- next database --
---**/
END
FETCH NEXT FROM bcks
INTO @.name, @.main
END
CLOSE bcks
DEALLOCATE bcks
/**---
---**/
/** Send message **/
/**---
---**/
SET @.message =3D @.message + 'Completed No: ' + CAST(@.set AS VARCHAR(2)) + ' backup on: ' + @.@.SERVERNAME + ' ' + CONVERT(VARCHAR(100), GETDATE(), 20)
SET @.SUBJECT =3D 'Backup report from ' + @.@.SERVERNAME
EXECUTE xp_sendmail @.Recipients =3D 'andras.jakus@.vodafone.com', --Your address here
@.Message =3D @.message,
@.Subject =3D @.SUBJECT
/**---
---**/
/** Drop alltemporary tables **/
/**---
---**/
DROP TABLE ##dbs
DROP TABLE #Dirs
/*******************************************************/
Change @.Recipients to your address
Create (Multi Server)job with one step: EXECUTE master.dbo.AdmSpBackup
Schedule the job: 07:00; 12:00; 16:00; 20:00; 22:00
>--Original Message--
>In addition to Hari's post:
>Weekly? That would never go in my installation. You are essentially saying that it is OK to lose up
>to one week worth of data. My baseline is database backup every day and log backup every hour.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sqlserver
>
>"Jay" <neessan_gen@.rediffmail.com> wrote in message news:048201c39daf$d6959230$a501280a@.phx.gbl...
>> Hi
>> I want to take a backup of SQL Server database on weekly
>> basis. How can I set up things on my server ? any script
>> will have to be written or any tool to be used ? pl.
>> help..
>> Jay
>
>.
>sql
Sunday, March 11, 2012
Backup Problem
I back up our database on a monthly basis and just recently the server had
to be upgraded and rebooted. It seems as though some data was lost.
When restarting does the database return to its orginal state or a previous
backed up version?
ThanksHello Donald !
Uncommited transaction are only rollbacked if you restart the server.
Why do think there is data lost ? Are there any hints ?
Jens Süßmeyer.
"Donald Harman" <donald.harman@.payglobal.com> schrieb im Newsbeitrag
news:##kAupgZDHA.656@.tk2msftngp13.phx.gbl...
> I back up our database on a monthly basis and just recently the server had
> to be upgraded and rebooted. It seems as though some data was lost.
> When restarting does the database return to its orginal state or a
previous
> backed up version?
> Thanks
>|||No clues,
I am relatively new to SQL Server and all should be smooth as just an entry
that should be in there no longer is.
I was wondering if by some weird reason the Database may have restarted and
restored an older
backed up version.
At this stage am none the wiser.
Still am able to restore data with a manual entry.
All is not lost but ? only '.
joys
thanks for your help
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:e3hchIhZDHA.2960@.tk2msftngp13.phx.gbl...
> Hello Donald !
> Uncommited transaction are only rollbacked if you restart the server.
> Why do think there is data lost ? Are there any hints ?
> Jens Süßmeyer.
> "Donald Harman" <donald.harman@.payglobal.com> schrieb im Newsbeitrag
> news:##kAupgZDHA.656@.tk2msftngp13.phx.gbl...
> > I back up our database on a monthly basis and just recently the server
had
> > to be upgraded and rebooted. It seems as though some data was lost.
> >
> > When restarting does the database return to its orginal state or a
> previous
> > backed up version?
> >
> > Thanks
> >
> >
>|||> I was wondering if by some weird reason the Database may have restarted and
> restored an older
> backed up version.
SQL Server doesn't do this. It does, however, rollback uncommitted transactions at startup, as
posted by Jens. It is very difficult to assist these cases "over the net". You might want to get
someone experienced with SQL Server to have a look at the machine. Or give a detailed post about
what has happened and how you determine your loss of data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Donald Harman" <donald.harman@.payglobal.com> wrote in message
news:eG9$GShZDHA.2256@.TK2MSFTNGP10.phx.gbl...
> No clues,
> I am relatively new to SQL Server and all should be smooth as just an entry
> that should be in there no longer is.
> I was wondering if by some weird reason the Database may have restarted and
> restored an older
> backed up version.
> At this stage am none the wiser.
> Still am able to restore data with a manual entry.
> All is not lost but ? only '.
> joys
> thanks for your help
> "Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
> news:e3hchIhZDHA.2960@.tk2msftngp13.phx.gbl...
> > Hello Donald !
> >
> > Uncommited transaction are only rollbacked if you restart the server.
> > Why do think there is data lost ? Are there any hints ?
> >
> > Jens Süßmeyer.
> >
> > "Donald Harman" <donald.harman@.payglobal.com> schrieb im Newsbeitrag
> > news:##kAupgZDHA.656@.tk2msftngp13.phx.gbl...
> > > I back up our database on a monthly basis and just recently the server
> had
> > > to be upgraded and rebooted. It seems as though some data was lost.
> > >
> > > When restarting does the database return to its orginal state or a
> > previous
> > > backed up version?
> > >
> > > Thanks
> > >
> > >
> >
> >
>
to be upgraded and rebooted. It seems as though some data was lost.
When restarting does the database return to its orginal state or a previous
backed up version?
ThanksHello Donald !
Uncommited transaction are only rollbacked if you restart the server.
Why do think there is data lost ? Are there any hints ?
Jens Süßmeyer.
"Donald Harman" <donald.harman@.payglobal.com> schrieb im Newsbeitrag
news:##kAupgZDHA.656@.tk2msftngp13.phx.gbl...
> I back up our database on a monthly basis and just recently the server had
> to be upgraded and rebooted. It seems as though some data was lost.
> When restarting does the database return to its orginal state or a
previous
> backed up version?
> Thanks
>|||No clues,
I am relatively new to SQL Server and all should be smooth as just an entry
that should be in there no longer is.
I was wondering if by some weird reason the Database may have restarted and
restored an older
backed up version.
At this stage am none the wiser.
Still am able to restore data with a manual entry.
All is not lost but ? only '.
joys
thanks for your help
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:e3hchIhZDHA.2960@.tk2msftngp13.phx.gbl...
> Hello Donald !
> Uncommited transaction are only rollbacked if you restart the server.
> Why do think there is data lost ? Are there any hints ?
> Jens Süßmeyer.
> "Donald Harman" <donald.harman@.payglobal.com> schrieb im Newsbeitrag
> news:##kAupgZDHA.656@.tk2msftngp13.phx.gbl...
> > I back up our database on a monthly basis and just recently the server
had
> > to be upgraded and rebooted. It seems as though some data was lost.
> >
> > When restarting does the database return to its orginal state or a
> previous
> > backed up version?
> >
> > Thanks
> >
> >
>|||> I was wondering if by some weird reason the Database may have restarted and
> restored an older
> backed up version.
SQL Server doesn't do this. It does, however, rollback uncommitted transactions at startup, as
posted by Jens. It is very difficult to assist these cases "over the net". You might want to get
someone experienced with SQL Server to have a look at the machine. Or give a detailed post about
what has happened and how you determine your loss of data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Donald Harman" <donald.harman@.payglobal.com> wrote in message
news:eG9$GShZDHA.2256@.TK2MSFTNGP10.phx.gbl...
> No clues,
> I am relatively new to SQL Server and all should be smooth as just an entry
> that should be in there no longer is.
> I was wondering if by some weird reason the Database may have restarted and
> restored an older
> backed up version.
> At this stage am none the wiser.
> Still am able to restore data with a manual entry.
> All is not lost but ? only '.
> joys
> thanks for your help
> "Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
> news:e3hchIhZDHA.2960@.tk2msftngp13.phx.gbl...
> > Hello Donald !
> >
> > Uncommited transaction are only rollbacked if you restart the server.
> > Why do think there is data lost ? Are there any hints ?
> >
> > Jens Süßmeyer.
> >
> > "Donald Harman" <donald.harman@.payglobal.com> schrieb im Newsbeitrag
> > news:##kAupgZDHA.656@.tk2msftngp13.phx.gbl...
> > > I back up our database on a monthly basis and just recently the server
> had
> > > to be upgraded and rebooted. It seems as though some data was lost.
> > >
> > > When restarting does the database return to its orginal state or a
> > previous
> > > backed up version?
> > >
> > > Thanks
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)