Friday, April 1, 2011

Backup and Restore From Sql to Local /Network Machine

Backup/Restore for local machine....

Declare
@dbName varchar(100)
Set @dbName = 'dbMurli'
Declare @filelocation varchar(Max)
Set @filelocation = 'D:/'+@dbName+'.bak';
BACKUP DATABASE @dbName TO DISK = @filelocation
Restore DATABASE @dbName FROM DISK = @filelocation

Backup/Restore for Network machine....

Declare @dbName varchar(100)
Set @dbName = 'dbMurli'
Declare @filelocation nvarchar(Max)
Set @filelocation = N'\\192.0.0.72\Share\db\'+@dbName+'.bak';
BACKUP DATABASE @dbName TO DISK = @filelocation
Restore DATABASE @dbName FROM DISK = @filelocation

If you Found

Error 3154: The backup set holds a backup of a database other than the existing database.

Solution is very simple and not as difficult as he was thinking. He was trying to restore the database on another existing active database.

Fix/WorkAround/Solution:

1) Use WITH REPLACE while using the RESTORE command.

2) Delete the older database which is conflicting and restore again using RESTORE command.

I understand my solution is little different than BOL but I use it to fix my database issue successfully.

3) Sample Example :
RESTORE DATABASE 'dbMurli'
FROM DISK = D:\dbMurli
.bak'
WITH REPLACE

Is Exists in SQL

For database check

if db_id('dbname') is not null

For table check

if object_id('object_name', 'U') is not null -- for table

For Procedure existing

if object_id('object_name', 'P') is not null -- for SP

Simple script to Restore all SQL Server databases

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
--BACKUP DATABASE @name TO DISK = @fileName
RESTORE DATABASE @name FROM DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

If you Found

Error 3154: The backup set holds a backup of a database other than the existing database.

Solution is very simple and not as difficult as he was thinking. He was trying to restore the database on another existing active database.

Fix/WorkAround/Solution:

1) Use WITH REPLACE while using the RESTORE command. View Example

2) Delete the older database which is conflicting and restore again using RESTORE command.

I understand my solution is little different than BOL but I use it to fix my database issue successfully.

3) Sample Example :
RESTORE DATABASE 'dbMurli'
FROM DISK = D:\dbMurli
.bak'
WITH REPLACE